package co.fitstart.weapp.api.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import com.demo2do.core.persistence.annotation.Dao;
import com.demo2do.core.persistence.annotation.Execute;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;

import co.fitstart.entity.coach.CoachRestCalendar;
import co.fitstart.entity.coach.CoachRestType;
import co.fitstart.entity.coach.CoachSigninStatistic;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.site.Site;

/**
 * 
 * @author haiyan_xiao
 *
 */
@Dao("hibernate")
public interface CoachDao {
	
	/**
	 * @Description: 根据教练获取教练未读消息的条数
	 * @param:  @param coach
	 * @param:  @param read
	 * @param:  @return 
	 * @return:  int
	 */
	@Query("SELECT COUNT(*) FROM PrincipalMessage WHERE disabled = false AND principal = :principal AND read = :read")
    public int count(@Variable("principal") Principal principal, @Variable("read") Boolean read);

    /**
     * 根据周几获取门店可选教练
     * @param site
     * @param dayOfWeek %1% 周一,%2% 周二,%3% 周三,%4% 周四,%5% 周五,%6% 周六,%7% 周日
     * @return
     */
    @Query("SELECT DISTINCT(principalReportRelation.coach) FROM PrincipalReportRelation principalReportRelation"
                + " WHERE principalReportRelation.coach IS NOT NULL"
                + " AND principalReportRelation.manager IS NOT NULL"
                + " AND principalReportRelation.coach.disabled = false"
                + " AND principalReportRelation.site = :site"
                + " AND (principalReportRelation.appointDays IS NULL OR principalReportRelation.appointDays LIKE :dayOfWeek)")
    public List<Principal> list(@Variable("site") Site site, @Variable("dayOfWeek") String dayOfWeek);

    /**
     * 列出各时间不可排课教练
     * @param restDate
     * @param coaches
     * @param duty
     * @return
     */
    @Query("SELECT new Map(restHour AS restHour, coach.id AS coachId) FROM CoachRestCalendar WHERE disabled = false AND restDate = :restDate AND coach IN (:coaches) AND coachRestType != :duty ORDER BY restHour ASC")
    public List<Map<String, Object>> listCalendar(@Variable("restDate") Date restDate, @Variable("coaches") List<Principal> coaches, @Variable("duty") CoachRestType duty);

    /**
     * 
     * @param restDate
     * @param coaches
     * @param duty
     * @param coachRestCalendar
     * @return
     */
    @Query("SELECT new Map(coachRestCalendar.restHour AS restHour, coachRestCalendar.coach.id AS coachId) FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.restDate = :restDate AND coachRestCalendar.coach IN (:coaches) AND coachRestCalendar.coachRestType != :duty AND coachRestCalendar != :coachRestCalendar ORDER BY coachRestCalendar.restHour ASC")
    public List<Map<String, Object>> listCalendar(@Variable("restDate") Date restDate, @Variable("coaches") List<Principal> coaches, @Variable("duty") CoachRestType duty, @Variable("coachRestCalendar") CoachRestCalendar coachRestCalendar);

    /**
     * 
     * @param coach
     * @param coachRestType
     * @param restDate
     * @param restHour
     * @return
     */
    @Query("FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND coachRestType = :coachRestType AND restDate = :restDate AND restHour = :restHour")
    public CoachRestCalendar loadCoachRestCalendar(@Variable("coach") Principal coach, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restDate") Date restDate, @Variable("restHour") String restHour);

    /**
     * 
     * @param coaches
     * @param coachRestType
     * @param restDate
     * @param restHours
     * @return
     */
    @Query("SELECT DISTINCT coachRestCalendar.coach FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.coach IN (:coaches) AND coachRestCalendar.coachRestType != :coachRestType AND coachRestCalendar.restDate = :restDate AND coachRestCalendar.restHour IN (:restHours)")
    public List<Principal> listBusyCoach(@Variable("coaches") List<Principal> coaches, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restDate") Date restDate, @Variable("restHours") List<String> restHours);

    /**
     * 
     * @param coaches
     * @param coachRestType
     * @param restDate
     * @param restHours
     * @param coachRestCalendar
     * @return
     */
    @Query("SELECT DISTINCT coach FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.coach IN (:coaches) AND coachRestCalendar.coachRestType != :coachRestType AND coachRestCalendar.restDate = :restDate AND coachRestCalendar.restHour IN (:restHours) AND coachRestCalendar != :coachRestCalendar")
    public List<Principal> listBusyCoach(@Variable("coaches") List<Principal> coaches, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restDate") Date restDate, @Variable("restHours") List<String> restHours, @Variable("coachRestCalendar") CoachRestCalendar coachRestCalendar);

    /**
     * 
     * @param site
     * @param dayOfWeek
     * @param coach
     * @return
     */
    @Query("SELECT principalReportRelation.coach FROM PrincipalReportRelation principalReportRelation"
                + " WHERE principalReportRelation.coach = :coach"
                + " AND principalReportRelation.manager IS NOT NULL"
                + " AND principalReportRelation.site = :site"
                + " AND (principalReportRelation.appointDays IS NULL OR principalReportRelation.appointDays LIKE :dayOfWeek)")
    public Principal load(@Variable("site") Site site, @Variable("dayOfWeek") String dayOfWeek, @Variable("coach") Principal coach);

    /**
     * 
     * @param coachRestType
     * @param coach
     * @param restDate
     * @param restHours
     * @return
     */
    @Query("SELECT COUNT(*) FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.coachRestType != :coachRestType AND coachRestCalendar.coach = :coach AND coachRestCalendar.restDate = :restDate AND coachRestCalendar.restHour IN (:restHours)")
    public int count(@Variable("coachRestType") CoachRestType coachRestType, @Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("restHours") List<String> restHours);

    /**
     * 
     * @param coachRestType
     * @param coach
     * @param restDate
     * @param restHours
     * @param coachRestCalendar
     * @return
     */
    @Query("SELECT COUNT(*) FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.coachRestType != :coachRestType AND coachRestCalendar.coach = :coach AND coachRestCalendar.restDate = :restDate AND coachRestCalendar.restHour IN (:restHours) AND coachRestCalendar != :coachRestCalendar")
    public int count(@Variable("coachRestType") CoachRestType coachRestType, @Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("restHours") List<String> restHours, @Variable("coachRestCalendar") CoachRestCalendar coachRestCalendar);

    /**
     * 
     * @param coach
     * @param month
     * @return
     */
    @Query("FROM CoachSigninStatistic WHERE principal = :coach AND month = :month")
    public CoachSigninStatistic loadCoachSigninStatistic(@Variable("coach") Principal coach, @Variable("month") String month);

    /**
     * 
     * @param mobile
     * @return
     */
    @Query("FROM Principal WHERE disabled = false AND authority = 'ROLE_COACH' AND enterprise = true AND mobile = :mobile")
    public Principal load(@Variable("mobile") String mobile);

    /**
     * 获取教练可预约门店
     * @param coach
     * @param dayOfWeek %1% 周一,%2% 周二,%3% 周三,%4% 周四,%5% 周五,%6% 周六,%7% 周日
     * @return
     */
    @Query("SELECT relation.site FROM PrincipalReportRelation relation WHERE relation.manager IS NOT NULL AND relation.coach = :coach AND (relation.appointDays IS NULL OR relation.appointDays LIKE :dayOfWeek)")
    public List<Site> listSite(@Variable("coach") Principal coach, @Variable("dayOfWeek") String dayOfWeek);

    /**
     * 
     * @param coach
     * @param restDate
     * @return
     */
    @Query("FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :restDate")
    public List<CoachRestCalendar> listCoachRestCalendar(@Variable("coach") Principal coach, @Variable("restDate") Date restDate);

    /**
     * 
     * @param coach
     * @param restDate
     * @return
     */
    @Query("SELECT restHour FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :restDate")
    public List<String> listRestTime(@Variable("coach") Principal coach, @Variable("restDate") Date restDate);

    /**
     * 
     * @param coach
     * @param restDate
     * @param coachRestType
     * @return
     */
    @Query("SELECT restHour FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :restDate AND coachRestType = :coachRestType")
    public List<String> listRestTime(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("coachRestType") CoachRestType coachRestType);

    /**
     * 
     * @param coach
     * @param restDate
     * @param times
     * @return
     */
    @Query("SELECT COUNT(*) FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :restDate AND restHour IN (:restTimes)")
    public int countCoachRestCalendar(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("restTimes") List<String> times);

    /**
     * 
     * @param coach
     * @param restDate
     * @param coachRestType
     * @param times
     * @return
     */
    @Query("SELECT COUNT(*) FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :restDate AND coachRestType != :coachRestType AND restHour IN (:restTimes)")
    public int countUnrest(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restTimes") List<String> times);

    /**
     * 
     * @param coach
     * @param restDate
     * @param coachRestType
     * @param times
     */
    @Execute("UPDATE CoachRestCalendar SET disabled = true WHERE disabled = false AND coach = :coach AND restDate = :restDate AND coachRestType = :coachRestType AND restHour IN (:restTimes)")
    public void disabledRest(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("coachRestType") CoachRestType coachRestType, @Variable("restTimes") List<String> times);

    /**
     * 
     * @param coach
     * @param restDate
     * @param coachRestType
     */
    @Execute("UPDATE CoachRestCalendar SET disabled = true WHERE disabled = false AND coach = :coach AND restDate = :restDate AND coachRestType = :coachRestType")
    public void disabledRest(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("coachRestType") CoachRestType coachRestType);

    /**
     * 
     * @param coach
     * @param restDate
     * @param coachRestType
     * @param today
     * @param now
     */
    @Execute("UPDATE CoachRestCalendar SET disabled = true WHERE disabled = false AND coach = :coach AND restDate = :restDate AND coachRestType = :coachRestType AND STR_TO_DATE(CONCAT(:today,restHour,':00'),'%Y-%m-%d %H:%i:%s') >= :now()")
    public void disabledTodayRest(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("coachRestType") CoachRestType coachRestType, @Variable("today") String today, @Variable("now") Date now);

    /**
     * 
     * @param coach
     * @param monday
     * @param sunday
     * @param coachRestType
     * @return
     */
    @Query("SELECT COUNT(*) FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate BETWEEN :start AND :end AND coachRestType = :coachRestType")
    public int countCoachRestCalendar(@Variable("coach") Principal coach, @Variable("start") Date start, @Variable("end") Date end, @Variable("coachRestType") CoachRestType coachRestType);

    /**
     * 
     * @param coach
     * @param restDate
     * @param coachRestType
     * @return
     */
    @Query("FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate = :restDate AND coachRestType = :coachRestType")
    public List<CoachRestCalendar> listCoachRestCalendar(@Variable("coach") Principal coach, @Variable("restDate") Date restDate, @Variable("coachRestType") CoachRestType coachRestType);

    /**
     * 
     * @param coach
     * @param start
     * @param end
     * @param coachRestType
     * @return
     */
    @Query("FROM CoachRestCalendar WHERE disabled = false AND coach = :coach AND restDate BETWEEN :start AND :end AND coachRestType = :coachRestType")
    public List<CoachRestCalendar> listCoachRestCalendar(@Variable("coach") Principal coach, @Variable("start") Date start, @Variable("end") Date end, @Variable("coachRestType") CoachRestType coachRestType);
}
